<?php

$HLA_Tables = array(

"hla_user"=>
    array(
        "create table hla_user
        (
            user_id nvarchar(20),
            user_password nvarchar(32),
            user_name nvarchar(50),
            user_level int,
            rowguid autoincrement
        )",
        "create unique index hla_user_uidx1 on hla_user (user_name)",
        "create index hla_user_idx1 on hla_user (user_name, user_password)",
        "insert into hla_user (user_id, user_password, user_name, user_level) values ('admin', '21232f297a57a5a743894a0e4a801fc3', 'Default Administrator', 0)",
    ),

"hla_param"=>
    array(
        "create table hla_param
        (
            param_key nvarchar(20),
            param_value nvarchar(120),
            rowguid autoincrement
        )",
        "create unique index hla_param_uidx1 on hla_param (param_key)",
        "insert into hla_param (param_key, param_value) values ('institution_name', '')",
        "insert into hla_param (param_key, param_value) values ('lib_name', '')",
        "insert into hla_param (param_key, param_value) values ('report_name', '')",
    ),

"hla_kit"=>
    array(
        "create table hla_kit
        (
            kit_id nvarchar(20),
            kit_name nvarchar(50),
            kit_sn nvarchar(20),
            kit_sample_image_count int,
            kit_desc nvarchar(200),
            kit_manual_numbers nvarchar(200),
            rowguid autoincrement
        )",
        "create unique index hla_kit_uidx1 on hla_kit (kit_id)",
    ),

"hla_serology"=>
    array(
        "create table hla_serology
        (
            kit_id nvarchar(20),
            serology_id nvarchar(20),
            serology_desc nvarchar(200),
            serology_file_name nvarchar(200),
            rowguid autoincrement
        )",
        "create unique index hla_serology_uidx1 on hla_serology (kit_id, serology_id)",
    ),

"hla_pattern"=>
    array(
        "create table hla_pattern
        (
            kit_id nvarchar(20),
            serology_id nvarchar(20),
            line_no int,
            serotype nvarchar(50),
            genotype nvarchar(200),
            primer_numbers nvarchar(200),
            serology_check nvarchar(10),
            frequency int,
            rowguid autoincrement
        )",
        "create unique index hla_pattern_uidx1 on hla_pattern (kit_id, serology_id, line_no)",
        "create index hla_pattern_idx1 on hla_pattern (serology_id, primer_numbers)",
    ),  // serology_check nvarchar(10),         -- ctrl, linkage

"hla_pattern_search"=>
    array(
        "create table hla_pattern_search
        (
            kit_id nvarchar(20),
            serology_id nvarchar(20),
            line_no int,
            genoindex nvarchar(20),
            rowguid autoincrement
        )",
        "create index hla_pattern_search_idx1 on hla_pattern_search (kit_id, serology_id, line_no)",
        "create index hla_pattern_search_idx2 on hla_pattern_search (genoindex)",
    ),

"hla_pattern_number"=>
    array(
        "create table hla_pattern_number
        (
            kit_id nvarchar(20),
            primer_number nvarchar(3),
            number_size nvarchar(20),
            serology_id nvarchar(20),
            cell_id nvarchar(10),
            fmt_col nvarchar(2),
            fmt_row nvarchar(3),
            thd_well nvarchar(3),
            thd_low int,
            thd_high int,
            rowguid autoincrement
        )",
        "create unique index hla_pattern_number_uidx1 on hla_pattern_number (kit_id, primer_number)",
        "create index hla_pattern_number_idx1 on hla_pattern_number (kit_id)",
    ),  // thd_line_no int,

"hla_sample"=>
    array(
        "create table hla_sample
        (
            sample_id nvarchar(20),
            patient_rowguid int,
            kit_id nvarchar(20),
            primer_numbers nvarchar(200),
            dna_ext_method nvarchar(30),
            dna_od nvarchar(12),
            lot_no nvarchar(20),
            lot_expires datetime,
            test_date datetime,
            test_by nvarchar(50),
            comment nvarchar(200),
            image_file_name memo,
            final nvarchar(1),
            rowguid autoincrement
        )",
        "create unique index hla_sample_uidx1 on hla_sample (sample_id)",
        "create index hla_sample_idx1 on hla_sample (patient_rowguid)",
        "create index hla_sample_idx2 on hla_sample (sample_id)",
        "create index hla_sample_idx3 on hla_sample (kit_id)",
        "create index hla_sample_idx4 on hla_sample (test_date)",
        "create index hla_sample_idx5 on hla_sample (test_by)",
    ),

"hla_sample_types"=>
    array(
        "create table hla_sample_types
        (
            sample_id nvarchar(20),
            serology_id nvarchar(20),
            line_no1 nvarchar(30),
            line_no2 nvarchar(30),
            primer_numbers nvarchar(100),
            selected nvarchar(1),
            frequency int,
            imperfect nvarchar(1),
            rowguid autoincrement
        )",
        "create index hla_sample_types_idx1 on hla_sample_types (sample_id)",
        "create index hla_sample_types_idx2 on hla_sample_types (serology_id)",
        "create index hla_sample_types_idx3 on hla_sample_types (line_no1)",
        "create index hla_sample_types_idx4 on hla_sample_types (line_no2)",
        "create index hla_sample_types_idx5 on hla_sample_types (selected)"
    ),

"hla_sample_types_search"=>
    array(
        "create table hla_sample_types_search
        (
            sample_id nvarchar(20),
            genoindex nvarchar(20),
            rowguid autoincrement
        )",
        "create index hla_sample_types_search_idx1 on hla_sample_types_search (sample_id)",
        "create index hla_sample_types_search_idx2 on hla_sample_types_search (genoindex)",
    ),

"hla_patient"=>
    array(
        "create table hla_patient
        (
            institution nvarchar(120),
            patient_id nvarchar(20),
            ethnic nvarchar(20),
            first_name nvarchar(50),
            last_name nvarchar(50),
            sex nvarchar(1),
            age int,
            rowguid autoincrement
        )",
        "create index hla_patient_idx1 on hla_patient (patient_id)",
        "create index hla_patient_idx2 on hla_patient (institution)",
        "create index hla_patient_idx3 on hla_patient (first_name, last_name)",
        "create index hla_patient_idx4 on hla_patient (sex)",
        "create index hla_patient_idx5 on hla_patient (age)"
    ),

"hla_old_sample"=>
    array(
        "create table hla_old_sample
        (
            Type1                          varchar(030),
            Type2                          varchar(030),
            Combine_Alleles                varchar(240),
            Type                           varchar(006),
            New_Mix_Num                    varchar(100),
            Allele_1                       varchar(030),
            Allele_2                       varchar(030),
            Allele_5253                    varchar(008),
            Allele_5352                    varchar(008),
            Search_1                       varchar(030),
            Search_2                       varchar(030),
            Institution                    varchar(120),
            Sample_ID                      varchar(016),
            Last_Name                      varchar(042),
            First_Name                     varchar(042),
            Sex                            varchar(008),
            Age                            varchar(003),
            Ethnic                         varchar(020),
            Kit_Name                       varchar(020),
            Kit_Lot_ID                     varchar(020),
            Kit_Expires                    varchar(012),
            DNA_OD                         varchar(012),
            DNA_Ext_Method                 varchar(030),
            Purpose                        varchar(150),
            Test_Date                      varchar(012),
            Review_Date                    varchar(012),
            Tested_By                      varchar(042),
            Review_By                      varchar(042),
            Comment                        varchar(200),
            primer_numbers nvarchar(200),
            rowguid autoincrement
        )",
        "create index hla_old_sample_idx1 on hla_old_sample (Sample_ID)",
        "create index hla_old_sample_idx2 on hla_old_sample (Kit_Name)",
        "create index hla_old_sample_idx3 on hla_old_sample (Tested_By)",
        "create index hla_old_sample_idx4 on hla_old_sample (Kit_Lot_ID)",
        "create index hla_old_sample_idx5 on hla_old_sample (Institution)",
        "create index hla_old_sample_idx6 on hla_old_sample (First_Name, Last_Name)",
    ),

"hla_old_sample_types_search"=>
    array(
        "create table hla_old_sample_types_search
        (
            sample_id nvarchar(20),
            genoindex nvarchar(20),
            rowguid autoincrement
        )",
        "create index hla_old_sample_types_search_idx1 on hla_old_sample_types_search (sample_id)",
        "create index hla_old_sample_types_search_idx2 on hla_old_sample_types_search (genoindex)",
    ),

);

?>